/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Model;

import Controllers.ControllerAdminHomepage;
import Controllers.ControllerDoctorHomepage;
import Controllers.ControllerReceptionHomepage;
import java.awt.HeadlessException;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;

/**
 *
 * @author BENSON
 */
public class MainModel {
    /**
     * Declaration of variables , TableRowSorter, arrayList
     */
    Connection conn=null;
    String querry;
    Statement st=null;
    TableRowSorter<TableModel> rowSorter;
    String text;
    PrintWriter outPutStream=null;
    String path="";
    File file=null;
    ArrayList<String>myArrayList=new ArrayList<>();
    
    /**
     * Method to validate receptionist username and password
     * @param repLogin
     */
    public void showReceptionist( Views.ReceptionLogin repLogin){
    try{
        Class.forName("com.mysql.jdbc.Driver");
        conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull","root","");
        querry="SELECT * FROM Receptionists WHERE rName like '%"+repLogin.getRUsername().getText()+"%' AND rPassword like '%"+repLogin.getRPassword().getText()+"%' ;" ;
        st=conn.prepareStatement(querry);

        ResultSet rs= st.executeQuery(querry);
        if (!rs.next() ) {
                JOptionPane.showMessageDialog(null," Invalid Username or Password ");
                
        }
        else {

            do {
                if((repLogin.getRUsername().getText().equals(rs.getString("rName")))||(repLogin.getRPassword().getText().equals(rs.getString("rPassword")))){
                    ControllerReceptionHomepage repHomepage= new ControllerReceptionHomepage();  
                    repHomepage.control();
                    repLogin.setVisible(false);
                }
            } while (rs.next());
        }
   st.close();
   conn.close();
  
    }  
    catch(ClassNotFoundException | SQLException | HeadlessException e){
    JOptionPane.showMessageDialog(null,e);
    }
    
    }
    
     /**
     * Method to validate admin username and password
     * 
     * @param v
     */
    
    public void showAdmin(Views.AdminLogin v){
    try{
        Class.forName("com.mysql.jdbc.Driver");
        conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull","root","");
        querry="SELECT * FROM Doctors WHERE dName like '%"+v.getAUsername().getText()+"%' AND dPassword like '%"+v.getAPassword().getText()+"%' ;" ;
        st=conn.prepareStatement(querry);

        ResultSet rs= st.executeQuery(querry);
        if (!rs.next() ) {
                JOptionPane.showMessageDialog(null," Invalid Username or Password ");
                
        }
        else {

            do {
                if((v.getAUsername().getText().equals(rs.getString("dName")))||(v.getAPassword().getText().equals(rs.getString("aPassword")))){
                    ControllerAdminHomepage adminHomepage= new ControllerAdminHomepage();  
                    adminHomepage.control();
                    v.setVisible(false);
                }
            } while (rs.next());
        }
   st.close();
   conn.close();
  
    }  
    catch(ClassNotFoundException | SQLException | HeadlessException e){
    JOptionPane.showMessageDialog(null,e);
    }
    
    }
    /**
     * Method to validate doctor username and password
     * 
     * @param docLogin
     */
    public void showDoctor(Views.DoctorLogin docLogin){
    try{
        Class.forName("com.mysql.jdbc.Driver");
        conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull","root","");
        querry="SELECT * FROM Doctors WHERE dName like '%"+docLogin.getDUsername().getText()+"%' AND dPassword like '%"+docLogin.getDPassword().getText()+"%' ;" ;
        st=conn.prepareStatement(querry);

        ResultSet rs= st.executeQuery(querry);
        if (!rs.next() ) {
                JOptionPane.showMessageDialog(null," Invalid Username or Password ");
                
        }
        else {

            do {
                if((docLogin.getDUsername().getText().equals(rs.getString("dName")))||(docLogin.getDPassword().getText().equals(rs.getString("dPassword")))){
                    ControllerDoctorHomepage docHomepage= new ControllerDoctorHomepage();  
                    docHomepage.control();
                    docLogin.setVisible(false);
                }
            } while (rs.next());
        }
   st.close();
   conn.close();
  
    }  
    catch(ClassNotFoundException | SQLException | HeadlessException e){
    JOptionPane.showMessageDialog(null,e);
    }
    
    }
    
    /**
     * Displays the patients in the database in a JTable
     * @param repHome 
     */
    
     public void showPatients(Views.ReceptionHomePage repHome) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull", "root", "");
            querry = "SELECT * FROM Patients ;";
            st = conn.prepareStatement(querry);

            ResultSet rs = st.executeQuery(querry);
            while (rs.next()) {
                Object[] temp = {rs.getString("patientNo"), rs.getString("patientName"), rs.getString("email"), rs.getString("phoneNO"),
                    rs.getString("emergencyPhoneNO"), rs.getString("country"), rs.getString("gender"), rs.getString("regDate")};
                repHome.getTable().addRow(temp);
            }
            st.close();
            conn.close();

        } catch (ClassNotFoundException | SQLException e) {
            JOptionPane.showMessageDialog(null, e);
        }

    }
     
     
     /**
      * Reads the appointments in the database and displays them in a  JTable
      * @param repHome 
      */
    public void showAppointments(Views.ReceptionHomePage repHome){
    try{
        Class.forName("com.mysql.jdbc.Driver");
        conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull","root","");
        querry="SELECT * FROM Appointments ;" ;
        st=conn.prepareStatement(querry);

        ResultSet rs= st.executeQuery(querry);
   while(rs.next()){
  
   Object[] temp = {rs.getString("appoinmentID"), rs.getString("patientNo"),rs.getString("patientName"),rs.getString("doctor"),
                    rs.getString("doctorReports"),rs.getString("appointmentDate")};
   repHome.getTable().addRow(temp);
   }
   st.close();
   conn.close();
  
    }  
    catch(ClassNotFoundException | SQLException e){
    JOptionPane.showMessageDialog(null,e);
    }
    
}
/**
 * Shows the details of the patient
 * @param repHome 
 */
    public void showAllDetails(Views.ReceptionHomePage repHome) {
        String key ;
        key = repHome.getSearchField().getText();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull", "root", "");
            String query = "SELECT * FROM Patients  WHERE * LIKE'%" + key + "%'";
            st = conn.prepareStatement(query);
            ResultSet rs = st.executeQuery(query);

            while (rs.next()) {

                st.close();
                conn.close();

            }
        } catch (ClassNotFoundException | SQLException e) {
            JOptionPane.showMessageDialog(null, e);
        }
    }
/**
 * deletes selected patient from the table and the database
 *  
 * @param repHome 
 */
    public void deleteSelectedPatient(Views.ReceptionHomePage repHome) {
        int row = repHome.getJTable().getSelectedRow();
        String selected = repHome.getTable().getValueAt(row, 0).toString();

        if (row >= 0) {
            repHome.getTable().removeRow(row);
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull", "root", "");
                String query = "delete from Patients where patientNo='" + selected + "' ";
                PreparedStatement ps = conn.prepareStatement(query);
                ps.executeUpdate();
                JOptionPane.showMessageDialog(null, "Patient deleted succesfully");
            } catch (ClassNotFoundException | SQLException | HeadlessException e) {
                JOptionPane.showMessageDialog(null, e);
            }
        }
    }

    /**
     * Modify selected patient in the table and in the database
     * 
     * @param repHome 
     */
    public void modifySelectedPatient(Views.ReceptionHomePage repHome) {
        int row = repHome.getJTable().getSelectedRow();
        String selected = repHome.getTable().getValueAt(row, 0).toString();

        if (row >= 0) {
            try {

                Class.forName("com.mysql.jdbc.Driver");
                conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull", "root", "");
                querry = ("UPDATE Patients SET patientName=? ,email =?,phoneNO =?,emergencyPhoneNO =?,country=? ,gender =?,regDate =?where patientNo like '%" + selected + "%';");

                PreparedStatement p = conn.prepareStatement(querry);

                p.setString(1, repHome.getTable().getValueAt(row, 1).toString());
                p.setString(2, repHome.getTable().getValueAt(row, 2).toString());
                p.setString(3, repHome.getTable().getValueAt(row, 3).toString());
                p.setString(4, repHome.getTable().getValueAt(row, 4).toString());
                p.setString(5, repHome.getTable().getValueAt(row, 5).toString());
                p.setString(6, repHome.getTable().getValueAt(row, 6).toString());
                p.setString(7, repHome.getTable().getValueAt(row, 7).toString());
                p.execute();
                JOptionPane.showMessageDialog(null, "Patient updated succesfully");
            } catch (ClassNotFoundException | SQLException | HeadlessException e) {
                JOptionPane.showMessageDialog(null, e);
            }
        }
    }
    
    /**
     * Method to add patients and write to database
     * @param addPatient
     */
    public void addPatients( Views.AddPatient addPatient){
    try{
       Class.forName("com.mysql.jdbc.Driver");
       conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull","root","");
       
        querry=("INSERT INTO Patients SET patientName=? ,email =?,phoneNO =?,emergencyPhoneNO =?,country=? ,gender =?,regDate =?;");
        
        try (PreparedStatement p = conn.prepareStatement(querry)) {
            java.sql.Timestamp date = new java.sql.Timestamp(new java.util.Date().getTime());
            
            p.setString(1, addPatient.getPatientName().getText());
            p.setString(2, addPatient.getPatientEmail().getText());
            p.setString(3, addPatient.getPatientPhoneNumber().getText());
            p.setString(4, addPatient.getPatientEmergencyNumber().getText());
            p.setString(5, addPatient.getPatientCountry().getText());
            p.setString(6, (addPatient.getPatientGender().getSelectedItem()).toString());
            p.setTimestamp(7, date);
            p.execute();
        }
    conn.close();
    JOptionPane.showMessageDialog(null, "patient has been added succesfully ");
    }  
    catch(ClassNotFoundException | SQLException | HeadlessException e){
        JOptionPane.showMessageDialog(null,e);
    }   
}
 
  public void addAppointments(Views.BookAppointment bookAppoint){
    try{
       Class.forName("com.mysql.jdbc.Driver");
       conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull","root","");
       
        querry=("INSERT INTO Appointments SET patientNo=? ,patientName =?,doctor =?,appointmentDate=?;");
        
        try (PreparedStatement p = conn.prepareStatement(querry)) {
            java.sql.Timestamp date = new java.sql.Timestamp(new java.util.Date().getTime());
            
            p.setString(1, bookAppoint.getPatientNum().getText());
            p.setString(2, bookAppoint.getPatientName().getText());
            p.setString(3, (bookAppoint.getSpecialist().getSelectedItem()).toString());
            p.setTimestamp(4, date);
            p.execute();
        }
    conn.close();
    JOptionPane.showMessageDialog(null, "Appointment has been booked succesfully ");
    }  
    catch(ClassNotFoundException | SQLException | HeadlessException e){
        JOptionPane.showMessageDialog(null,e);
    } 
}
 
    /**
     * Specifies the path to save a exported report
     * @param null
     */
 private void saveReport() {
    JFrame parentFrame = new JFrame();
 
    JFileChooser fileChooser = new JFileChooser();
    fileChooser.setDialogTitle("SPECIFY FILE TO SAVE");   
 
    int userSelection = fileChooser.showSaveDialog(parentFrame);
 
    if (userSelection == JFileChooser.APPROVE_OPTION) {
        File fileToSave = fileChooser.getSelectedFile();
        path=fileToSave.getAbsolutePath();
    }
    file = new File(path);
}
 
  /**
     * Method to print all the scheduled appointments
     * 
     * @param docHome
     */
 
    public void printAppointments(Views.DoctorHomePage docHome){
    try{
        String specialist =docHome.getTheSpecialist().getSelectedItem().toString();
        Class.forName("com.mysql.jdbc.Driver");
        conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull","root","");
        querry="SELECT * FROM Appointments where doctor like '%" + specialist + "%';" ;
        st=conn.prepareStatement(querry);

        try{
            saveReport();
            outPutStream=new PrintWriter(new FileOutputStream(path, true));

        }
        catch(FileNotFoundException  e){
            JOptionPane.showMessageDialog(null,e.toString());
        }
        ResultSet rs= st.executeQuery(querry);
   while(rs.next()){ 
   outPutStream.println( rs.getString("appoinmentID")+"  "+ rs.getString("patientNo")+"  "+rs.getString("patientName")+"  "+rs.getString("doctor")
           +" " +rs.getString("doctorReports")+"  "+rs.getString("appointmentDate"));
   
   }
   JOptionPane.showMessageDialog(null,"Saving Completed");
        
   outPutStream.close();
   st.close();
   conn.close();
    }  
    catch(ClassNotFoundException | SQLException | HeadlessException e){
    JOptionPane.showMessageDialog(null,e);
    }
}
  
/**
 * 
 * Deletes the selected receptionist from the JTable and the database - from the JTable
 * @param adminHome 
 */
    
public void deleteSelectedReceptionist(Views.AdminHomePage adminHome) {
        int row = adminHome.getJTable().getSelectedRow();
        String selected = adminHome.getTable().getValueAt(row, 0).toString();

        if (row >= 0) {
            adminHome.getTable().removeRow(row);
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull", "root", "");
                String query = "delete from Receptionists where rID like'%" + selected + "%' ";
                PreparedStatement ps = conn.prepareStatement(query);
                ps.executeUpdate();
                JOptionPane.showMessageDialog(null, "Receptionist deleted succesfully");
            } catch (ClassNotFoundException | SQLException | HeadlessException e) {
                JOptionPane.showMessageDialog(null, e);
            }
        }
    }
   
/**
 * 
 * Deletes selected doctor from the JTable and the Database- from the JTable
 * @param adminHome 
 */


public void deleteSelectedDoctor(Views.AdminHomePage adminHome) {
        int row = adminHome.getJTable().getSelectedRow();
        String selected = adminHome.getTable().getValueAt(row, 0).toString();

        if (row >= 0) {
            adminHome.getTable().removeRow(row);
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull", "root", "");
                String query = "delete from Doctors where dID like'%" + selected + "&' ";
                PreparedStatement ps = conn.prepareStatement(query);
                ps.executeUpdate();
                JOptionPane.showMessageDialog(null, "Doctor deleted succesfully");
            } catch (ClassNotFoundException | SQLException | HeadlessException e) {
                JOptionPane.showMessageDialog(null, e);
            }
        }
    }
    
/**
 * Modify selected receptionist from the JTable and the Database- (from the JTable)
 * @param adminHome 
 */


public void modifySelectedReceptionist(Views.AdminHomePage adminHome) {
        int row = adminHome.getJTable().getSelectedRow();
        String selected = adminHome.getTable().getValueAt(row, 0).toString();

        if (row >= 0) {
            try {

                Class.forName("com.mysql.jdbc.Driver");
                conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull", "root", "");
                querry = ("UPDATE Receptionists SET rName=? ,rEmail =? where rID like '%" + selected + "%';");

                PreparedStatement p = conn.prepareStatement(querry);

                p.setString(1, adminHome.getTable().getValueAt(row, 1).toString());
                p.setString(2, adminHome.getTable().getValueAt(row, 2).toString());
                p.execute();
                JOptionPane.showMessageDialog(null, "Receptionist update succesfully");
            } catch (ClassNotFoundException | SQLException | HeadlessException e) {
                JOptionPane.showMessageDialog(null, e);
            }
        }
}
   
/**
 * Modify selected doctor from the JTable and the Database- (from the JTable)
 * @param adminHome 
 */


public void modifySelectedDoctor(Views.AdminHomePage adminHome) {
        int row = adminHome.getJTable().getSelectedRow();
        String selected = adminHome.getTable().getValueAt(row, 0).toString();

        if (row >= 0) {
            try { 

                Class.forName("com.mysql.jdbc.Driver");
                conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull", "root", "");
                querry = ("UPDATE Doctors SET dName=? ,dEmail =? where dID like '%" + selected + "%';");

                PreparedStatement p = conn.prepareStatement(querry);

                p.setString(1, adminHome.getTable().getValueAt(row, 1).toString());
                p.setString(2, adminHome.getTable().getValueAt(row, 2).toString());
                p.execute();
                JOptionPane.showMessageDialog(null, "Doctor updated succesfully");
            } catch (ClassNotFoundException | SQLException | HeadlessException e) {
                JOptionPane.showMessageDialog(null, e);
            }
        }
}
    
/**
 * Displays the administrators from the database to a JList
 * @param displayAdmin 
 */      
     
public void listAdmins(Views.ListAdmins displayAdmin){
        try{
            String list;
            Class.forName("com.mysql.jdbc.Driver");
            conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull","root","");
            list="SELECT * FROM Admin ;" ;
            st=conn.prepareStatement(list);

            ResultSet rs= st.executeQuery(list);
            
            while(rs.next()){
              myArrayList.add(rs.getString("aID") + "   " + rs.getString("aName") + "   "+ rs.getString("aEmail"));
            }
            
            Iterator<String> i =myArrayList.iterator(); 
            while(i.hasNext()){
            displayAdmin.getListModel().addElement(i.next());
            }
            st.close();
            conn.close();
        }
        catch(ClassNotFoundException | SQLException e){
            JOptionPane.showMessageDialog(null,e);
        }
}

/**
 * Prints the ticket of a selected patient(JTable) by picking the details from the database
 * @param printTick
 * @param selected 
 */
     
public void printTicketSelectedPatient(Views.printTicket printTick,String selected) {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull", "root", "");
                String query = "SELECT * from Appointments where appoinmentID like'%" + selected + "%' ";
                st=conn.prepareStatement(query);
                ResultSet rs= st.executeQuery(query);

                while(rs.next()){
                   printTick.getListModel().addElement("Appointment ID");
                   printTick.getListModel().addElement(rs.getString("appoinmentID")); 
                   printTick.getListModel().addElement("Patient ID");
                   printTick.getListModel().addElement(rs.getString("patientNo"));
                   printTick.getListModel().addElement("Patient Name");
                   printTick.getListModel().addElement(rs.getString("patientName"));
                   printTick.getListModel().addElement("Doctor ID");
                   printTick.getListModel().addElement(rs.getString("doctor"));
                   printTick.getListModel().addElement("Appointment Date");
                   printTick.getListModel().addElement(rs.getString("appointmentDate"));
                   
                }
                } catch (ClassNotFoundException | SQLException e) {
                    JOptionPane.showMessageDialog(null, e);
             }
 }
     
    /**
     * Adds a doctor to the database
     * @param addDoc 
     */

    
public void addDoctors(Views.AddDoctor addDoc){
    try{
       Class.forName("com.mysql.jdbc.Driver");
       conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull","root","");
       
        querry=("INSERT INTO Doctors SET dName=? ,dEmail =?,dPassword =?;");
        PreparedStatement p =conn.prepareStatement(querry);
        
        p.setString(1, addDoc.getDoctorName().getText());
        p.setString(2, addDoc.getDoctorEmail().getText());
        p.setString(3, addDoc.getDoctorPassword().getText());
        p.execute();
               
    p.close();
    conn.close();
    JOptionPane.showMessageDialog(null, "Doctor has been added succesfully ");
    }  
    catch(Exception e){
        JOptionPane.showMessageDialog(null,e);
    }   
}
    
       /**
      * Method to add a receptionist to the database
     * @param addRep
      */
    
public void addReceptionist(Views.AddReception addRep){
    try{
       Class.forName("com.mysql.jdbc.Driver");
       conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull","root","");
       
        querry=("INSERT INTO Receptionists SET rName=? ,rEmail =?,rPassword =?;");
        try (PreparedStatement p = conn.prepareStatement(querry)) {
            p.setString(1, addRep.getReceptionName().getText());
            p.setString(2, addRep.getReceptionEmail().getText());
            p.setString(3, addRep.getReceptionPassword().getText());
            p.execute();
        }
    conn.close();
    JOptionPane.showMessageDialog(null, "Receptionist has been added succesfully ");
    }  
    catch(ClassNotFoundException | SQLException | HeadlessException e){
        JOptionPane.showMessageDialog(null,e);
    }   
}
    
    /**
     * Adds an Admin to the database
     * @param addA 
     */
    public void addAdmins(Views.AddAdmin addA){
    try{
       Class.forName("com.mysql.jdbc.Driver");
       conn =(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/hosiManagerDb?zeroDateTimeBehavior=convertToNull","root","");
       
        querry=("INSERT INTO Admin SET aName=? ,aEmail =?,aPassword =?;");
        try (PreparedStatement p = conn.prepareStatement(querry)) {
            p.setString(1, addA.getAdminName().getText());
            p.setString(2, addA.getAdminEmail().getText());
            p.setString(3, addA.getAdminPassword().getText());
            p.execute();
        }
    conn.close();
    JOptionPane.showMessageDialog(null, "Admin has been added succesfully ");
    }  
    catch(ClassNotFoundException | SQLException | HeadlessException e){
        JOptionPane.showMessageDialog(null,e);
    }   
    }
 }

